# -*- coding:utf-8 -*-
import os.path
import re

import pandas as pd
from infrastructure.utils.dbUtil import SqliteUtil, SqliteConfig
from config.baseConfiguration import Sqlite3Config, ReportConfig


config = SqliteConfig(dbPath=Sqlite3Config.dbPath)

def groupBoardsView(group_id, groupName, sdti=None):
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
            select b.dti as '交易日期'
                    ,i.name as 股票名称, i.code as 股票编码
                    ,case when b.type = 1 then '涨停' else '跌停' end as 封板类型
                    ,b.count as 连扳数
                    ,b.jtjb as 几天几板
                    ,b.rise as 涨幅
                    ,b.price as 现价
                    ,b.first_time as 首次封板时间
                    ,b.last_time as 最终封板时间
                    ,b.open_count as 开板次数
                    ,round(b.board_turnover/100000000, 2) as '封单额(亿)'
                    ,round(b.market_capitalization/100000000, 2) as '流通市值(亿)'
                    ,round(b.cje/100000000, 2) as '成交额(亿)'
                    ,b.fcb as 封成比
                    ,b.turnoverr as 换手
                    ,b.board_state as 封板状态
                    ,round(m.zf_open, 2) as 次日开盘涨幅
                    ,round(m.zf_high, 2) as 次日最高涨幅
                    ,round(m.zf_low, 2) as 次日最低涨幅
                    ,round(m.zf_close, 2) as 次日收盘涨幅
                    ,(select group_concat(r, '+')
                        from (
                            select '['|| bl.group_id||']'||gi.name as r
                            from stock_board_logic bl left join group_info gi on bl.group_id=gi.id
                            where bl.dti=b.dti and bl.code=b.code and bl.type=0
                        )) as 所属板块
                    ,(select group_concat(r, '\n')
                        from (
                            select bl.info as r
                            from stock_board_logic bl 
                            where bl.dti=b.dti and bl.code=b.code and bl.type=1
                        )) as 涨停逻辑
            from stock_board b 
            left join stock_board_logic l on b.dti = l.dti and b.code=l.code
            left join stock_move m on m.code=b.code and m.dti=b.tdti
            left join stock_info i on b.code = i.code
            where 1=1 and l.group_id={group_id}
            --     and b.dti in ()
            --     and b.code = ''
            order by b.dti desc, b.first_time asc
            """
    if sdti is not None and sdti > 0:
        sql = sql.replace("1=1", f"b.dti >= {sdti}")
    boards = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(boards) == 0:
        return
    else:
        boards = [list(g) for g in boards]

    df = pd.DataFrame(boards, columns=columns)
    if sdti is not None and sdti > 0:
        filename = f'[{group_id}_{groupName}]{str(sdti)}.xlsx'
    else:
        filename = f'{group_id}_{groupName}.xlsx'
    filename = re.sub(r'[\/:*?"<>|]', '_', filename)
    filename = os.path.join(ReportConfig.reportPath, filename)
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name="封板分析", index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name="封板分析", index=False)


def groupRiseView():
    db = SqliteUtil.get_instance(dbConfig=config)
    griseSql = f"select group_id, sdti from group_rise r where edti is null"
    griseR = db.get_all(griseSql)
    if griseR is not None and len(griseR) > 0:
        for r in griseR:
            group_id = r[0]
            sdti = r[1]

            sql = f"""
                select
                    r.dti as '交易日期'
                    ,i.name as 板块名称
                    ,r.group_id as 板块id
                    ,r.ulbc as 涨停数
                    ,r.dlbc as 跌停数
                    ,r.mculbc as 连扳高度
                    ,r.logic as 逻辑
                    ,r.sbs as 首板数
                    ,r.lb2s as '2连扳数'
                    ,r.lb3s as '3连扳数'
                    ,r.lb4ps as '3+连数'
                    ,r.ubtop as 'top'
                    ,r.ub5d as '5top'
                    ,r.ub10d as '10top'
                    ,r.ub20d as '20top'
                    ,r.ub30d as '30top'
                    ,r.ub60d as '60top'
                    ,r.ub120d as '120top'
                    ,r.ub240d as '240top'
                    ,r.ub480d as '480top'
                    ,t.tip as 提示
                    ,t.desc as 提示描述
                from group_rotation r 
                    left join group_info i on r.group_id=i.id
                    left join stock_tips t on r.dti=t.dti and t.type=2 and t.tid=r.group_id 
                where r.dti >= {sdti} and r.group_id ={group_id}
                order by r.dti desc
                """

            groups = db.get_all(sql)
            columns = list(zip(*db.cur.description))[0]
            if len(groups) == 0:
                return
            else:
                groups = [list(g) for g in groups]

            df = pd.DataFrame(groups, columns=columns)
            groupName = groups[0][1]
            filename = f'[{group_id}_{groupName}]{str(sdti)}.xlsx'
            filename = re.sub(r'[\/:*?"<>|]', '_', filename)
            filename = os.path.join(ReportConfig.reportPath, filename)
            if os.path.exists(filename):
                with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
                    df.to_excel(writer, sheet_name="板块轮动", index=False)
            else:
                with pd.ExcelWriter(filename, mode='w') as writer:
                    df.to_excel(writer, sheet_name="板块轮动", index=False)

            groupBoardsView(group_id, groupName, sdti)


def groupRotationView(group_id):
    db = SqliteUtil.get_instance(dbConfig=config)
    sql = f"""
        select
            r.dti as '交易日期'
            ,i.name as 板块名称
            ,r.ulbc as 涨停数
            ,r.dlbc as 跌停数
            ,r.mculbc as 连扳高度
            ,r.logic as 逻辑
            ,r.sbs as 首板数
            ,r.lb2s as '2连扳数'
            ,r.lb3s as '3连扳数'
            ,r.lb4ps as '3+连数'
            ,r.ubtop as 'top'
            ,r.ub5d as '5top'
            ,r.ub10d as '10top'
            ,r.ub20d as '20top'
            ,r.ub30d as '30top'
            ,r.ub60d as '60top'
            ,r.ub120d as '120top'
            ,r.ub240d as '240top'
            ,r.ub480d as '480top'
            ,t.tip as 提示
            ,t.desc as 提示描述
        from group_rotation r left join group_info i on r.group_id=i.id
            left join stock_tips t on r.dti=t.dti and t.type=2 and t.tid=r.group_id 
        where r.group_id ={group_id}
        order by r.dti desc
        """

    groups = db.get_all(sql)
    columns = list(zip(*db.cur.description))[0]
    if len(groups) == 0:
        return
    else:
        groups = [list(g) for g in groups]

    groupName = groups[0][1]
    df = pd.DataFrame(groups, columns=columns).drop(columns=["板块名称"])
    filename = f'{group_id}_{groupName}.xlsx'
    filename = re.sub(r'[\/:*?"<>|]', '_', filename)
    filename = os.path.join(ReportConfig.reportPath, filename)
    if os.path.exists(filename):
        with pd.ExcelWriter(filename, mode='a', if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name="板块轮动", index=False)
    else:
        with pd.ExcelWriter(filename, mode='w') as writer:
            df.to_excel(writer, sheet_name="板块轮动", index=False)

    groupBoardsView(group_id, groupName)


def groupRotationPointView(groupIdStr: str):
    if groupIdStr is None or len(groupIdStr) == 0:
        return
    groupIds = groupIdStr.split(',')
    for groupId in groupIds:
        groupRotationView(groupId)


if __name__ == "__main__":
    # group_ids = []
    group_ids = [38, 330, 420, 176, 310, 351]
    if group_ids is not None and len(group_ids) > 0:
        for group_id in group_ids:
            groupRotationView(group_id)
    else:
        # groupRiseView()
        pass